![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Isolate Sequential I/OsIsolating the sequential I/Os is very importanteven with a disk array. As you learned in Chapter 14, Advanced Disk I/O Concepts, a sequential access to a disk reduces the time spent seeking between tracks. The seek time can be a large percentage of the time it takes to do an I/O. By reducing the head movement, you can get both a higher throughput on the disks and a faster response timeboth of which are important in an RDBMS environment. Here is a partial list of the things that are handled sequentially in an RDBMS system:
Full-table scans are mostly sequential in nature. Chained and migrated rows cause the sequential nature of the scan to be broken up; so does fragmentation of the database. As these problem areas are reduced or eliminated, the table scans become more sequential. By isolating the sequential I/Os to a particular disk volume, the performance of that volume can be quite high. However, anything that causes random I/Os on the sequential volumes eliminates those performance gains. To take best advantage of space, you can use the OS volume as a sequential volume if you determine that the system is used only for RDBMS processing, if very few OS activities produce I/Os on that volume, and if the system does not page or swap (that is, if the page/swap file is on the same volume). Distribute Random I/OsYou should distribute random I/Os across as many physical disks as possible. Doing so spreads out the load among many disks and enables the disk array to process as many random I/Os as those disks allow. If a single disk in the array can handle 50 random I/Os per second, it is reasonable to assume that an array of four similar disks can handle 200 random I/Os per second. When using a disk array, you do not have to split different types of data files among different logical volumes. In fact, you should do the opposite. When using a disk array, put all the data filesand their indexeson the same logical volume. The exception to this guideline is a decision support or batch processing system. The reason it has always been recommended that indexes and data files be separated was to allow for simultaneous accesses. But simultaneous access is what disk arrays excel at. In fact, the more simultaneous accesses you make to the disk array (until saturation), the better the overall throughput.
Load balancing of different data files is also unnecessary when using a disk array. It has always been a difficult task for the database administrator to try to balance each data file so that the I/O rates for each disk drive are balanced. Usually, that task is ineffective because of the changing nature of the database workload. By placing all the data files and indexes across a large striped volume, you are guaranteed to get a better load balance than you can get by hand. In a striped disk configuration, all disks average the same number of I/Os per second, regardless of the configuration and workload. Earlier, I mentioned that the exception to the rule that data and indexes should be configured on the same logical volume is with the decision support and batch processing systems. Decision support and batch processing typically involve bulk loads and batch updates. A typical scenario is for data to be extracted from the production OLTP systems on a regular basis and loaded onto the DSS (decision support system). During the database load (which is usually time critical), these four sequential operations may happen simultaneously:
In a DSS or batch processing system, it is a good idea to separate the data and index files on separate logical volumes for load purposes. The data generated from a detailed report file can also cause a significant amount of sequential I/O. If the batch report is the only job being run on the system, the report can be written to the same volume as the redo log file or to the batch load volume because neither of these are active during the batch processing job. By configuring a DSS system as just described, you lose some performance when you run queries because the load is not as well balanced as it would have been if the indexes and data shared the same drives. This minor performance loss is well made up for by the fact that loading is significantly faster.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |